package nbcp.myoql.code.generator.db.mysql

import nbcp.base.comm.*
import nbcp.base.db.*
import nbcp.base.db.annotation.*
import nbcp.base.enums.*
import nbcp.base.extend.*
import nbcp.base.utils.*
import nbcp.myoql.code.generator.compareDbName
import nbcp.myoql.code.generator.db.mysql.model.*
import nbcp.myoql.code.generator.db.sql.ISqlMetadata
import nbcp.myoql.code.generator.tool.*
import nbcp.myoql.db.*
import nbcp.myoql.db.comm.*
import nbcp.myoql.db.enums.*
import nbcp.myoql.db.sql.annotation.*
import nbcp.myoql.db.sql.base.*
import nbcp.myoql.db.sql.enums.DbType
import org.slf4j.LoggerFactory
import java.lang.reflect.Field
import java.time.LocalDateTime
import java.util.*


/**
 * MySql 实体生成器
 */
object SqlDbEntityGenerator {
    private val logger = LoggerFactory.getLogger(this::class.java)

    @JvmStatic
    fun db2Entity() = DbEntityBuilder();


    /**
     * 生成数据库表的实体代码。
     * @param db: 数据库名
     */
    class DbEntityBuilder() {

        private var tableCallback: ((String) -> Boolean)? = null

        fun whereTable(tableCallback: ((String) -> Boolean)?): DbEntityBuilder {
            this.tableCallback = tableCallback;
            return this;
        }

        /**
         * TODO: 需要好好整理一下。
         *
         * @param uniteGroupName 统一组名, 如果要分组，则把统一组名置为空串
         */
        fun toMarkdown(
            uniteGroupName: String = "",
            groupMap: StringMap = StringMap()
        ): List<NameCodeInfo> {
            var ret = mutableListOf<NameCodeInfo>()
            var data = getTablesData();

            //先对 group分组
            data.groupBy { uniteGroupName.AsString { groupMap.getOrDefault(it.group, it.group) } }
                .forEach {
                    var group = it.key
                    var entitys = it.value.sortedBy { it.name }

                    var groupEntitys = GroupEntitiesCodeTemplateData("", group, entitys);

                    var code = FreemarkerUtil.process(
                        "/markdown-template/tables-markdown.ftl",
                        groupEntitys
                    );
                    ret.add(NameCodeInfo(group, code));
                }


            return ret;
        }

        /**
         * 生成数据库表的实体代码。
         */
        fun toKotlinCode(packageName: String, groupFunc: ((EntityDbItemData) -> String)? = null): List<NameCodeInfo> {
            var ret = mutableListOf<NameCodeInfo>()
            var data = getTablesData();

            //先对 group分组
            data.groupBy { groupFunc?.invoke(it) ?: it.group }
                .forEach {
                    var group = it.key
                    var entitys = it.value

//                    var map = JsonMap(
//                        "entitys" to entitys
//                    )

                    var groupEntites = GroupEntitiesCodeTemplateData(packageName, group, entitys);

                    var code = FreemarkerUtil.process(
                        "/myoql-template/mysql/mysql-myoql-entity.ftl",
                        groupEntites
                    );
                    ret.add(NameCodeInfo().apply {
                        this.name = group
                        this.code = code
                    });
                }

            return ret;
        }

        fun toJpaCode(packageName: String, vararg baseEntityClass: Class<*>): List<NameCodeInfo> {
            var ret = mutableListOf<NameCodeInfo>()
            var data = getTablesData();

            //先对 group分组
            data.forEach {
                var entity = it
//                var map = JsonMap(
//                        "package" to packageName,
//                        "package_base" to packageName.split(".").take(2).joinToString("."),
//                        "entity" to entity,
//                        "field_name" to field_name()
//                )

                var entInfo = BaseEntityInfo(entity, baseEntityClass)
                var code = """package ${packageName};
import ${packageName.split(".").take(2).joinToString(".")}.*;
import lombok.*;
import java.time.*;
import java.util.*;
import nbcp.myoql.db.*;
import java.lang.*;

/**
* Created by CodeGenerator at ${LocalDateTime.now().AsString()}
*/
@Data
@NoArgsConstructor
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = true)
@Entity(name = "${entity.name}")
@DbName(name = "${entity.name}")
@Cn("${entity.simpleComment}")
public class ${entity.className} ${entInfo.getBaseClasseString()} ${entInfo.getBaseInterfaceString()} {
${
                    entInfo.getJpaStyleFields().joinToString("\n")
                        .ToTab(1)
                }
}
"""

                ret.add(NameCodeInfo(entity.name, code));
            }

            return ret;
        }


        fun getTablesData(): List<EntityDbItemData> {
            var ret = mutableListOf<EntityDbItemData>()

            var tables = ISqlMetadata.instance.getTables().toMutableList()
            var columns = ISqlMetadata.instance.getColumns().toMutableList()
            var indexes = ISqlMetadata.instance.getIndexes().toMutableList()

            tables.sortWith { a, b -> compareDbName(a.tableName, b.tableName) }

            columns.sortWith { a, b -> compareDbName(a.columnName, b.columnName) }


            tables.filter { tableMap ->
                if (tableCallback != null) {
                    return@filter tableCallback!!(tableMap.tableName!!)
                }
                return@filter true;
            }

                .forEach { tableMap ->
                    var tableData = EntityDbItemData()

                    tableData.name = tableMap.tableName!!;
                    tableData.comment = tableMap.tableComment.AsString()
                        .replace("\r\n", " ")
                        .replace('\n', ' ')
                        .replace('\"', '＂')
                        .replace('\$', '＄')
                        .replace('#', '＃')

                    var myIndex = indexes
                        .filter { it.tableName == tableData.name }
                        .groupBy { it.indexName }


                    var aloneIndex = myIndex
                        .filter { it.value.size == 1 }
                        .map { it.value.first().columnName }

                    var unionIndex = myIndex.filter { it.value.size > 1 }

                    tableData.auks = unionIndex
                        .map { it.value.map { it.columnName }.joinToString(",") }
                        .toTypedArray()


                    columns.filter { column -> column.tableName == tableData.name }
                        .forEachIndexed colMap@{ index, columnMap ->

                            var columnName = columnMap.columnName
                            var columnSqlType = columnMap.columnSqlType.AsString()
                            var columnComment = columnMap.columnComment.AsString()
                                .replace("\r\n", " ")
                                .replace('\n', ' ')
                                .replace('\"', '＂')
                                .replace('\$', '＄')
                                .replace('#', '＃')


                            var columnData = EntityDbItemFieldData()
                            columnData.sqlType = columnSqlType
                            columnData.name = columnName
                            columnData.index = (index + 1).toString()
                            columnData.comment = columnComment
                            columnData.charLength = columnMap.charLength
                            columnData.dbType = ISqlMetadata.instance.getDbType(columnSqlType)

                            columnData.isPrimary = columnMap.isPrimary
                            columnData.isUnique = columnMap.isUnique

                            columnData.autoInc = columnMap.isAutoIncrement
                            columnData.isVirtual = columnMap.isVirtual

                            if (aloneIndex.contains(columnName)) {
                                columnData.isUnique = true
                            }

                            tableData.columns.add(columnData)
                        }

                    tableData.columns.sortWith { a, b ->

                        if (a.isPrimary) {
                            return@sortWith -1;
                        }
                        if (b.isPrimary) {
                            return@sortWith 1;
                        }

                        if (a.isUnique) {
                            return@sortWith -1;
                        }
                        if (b.isUnique) {
                            return@sortWith 1;
                        }

                        return@sortWith compareDbName(a.name, b.name)
                    }


                    var index = 0;
                    for (column in tableData.columns) {
                        index++;
                        column.index = index.toString();
                    }

                    ret.add(tableData)
                }

            return ret;
        }
    }


    private fun getVarcharLen(field: Field): Int {
        var len = field.getAnnotation(DataLength::class.java);
        if (len != null) {
            return len.value
        }

        if (field.name.contains("id")) return 48;
        if (field.name.contains("code")) return 48;
        if (field.name.contains("name")) return 64;
        if (field.name.contains("data")) return 256;
        return 64
    }

    private fun getEnumItems(column: Class<*>): String {
        if (column.isEnum == false) return ""

        return column.enumConstants.map { it.toString() }.joinToString(",")
    }

    fun getColumnDefine(
        tableName: String,
        property: Field,
        nameType: NameMappingTypeEnum = NameMappingTypeEnum.ORIGIN,
        pFieldName: String = "",
        pCn: String = ""
    ): Pair<List<String>, List<String>> {
        var list = mutableListOf<String>()
        var checks = mutableListOf<String>()
        var columnName = pFieldName + nameType.getResult(property.name);
        var propertyType = property.type as Class<*>

        var dbType = DbType.of(propertyType);
        var type = property.getAnnotation(SqlColumnType::class.java)?.value
            .AsString {
                dbType.toMySqlTypeString(getVarcharLen(property), getEnumItems(propertyType))
            }

        var comment = arrayOf(
            pCn,
            property.getAnnotation(Cn::class.java)?.value.AsString()
        ).filter { it.HasValue }
            .joinToString(" ")

        var spreadColumn = property.getAnnotation(SqlSpreadColumn::class.java);
        if (spreadColumn != null) {
            propertyType.AllFields.forEach {
                getColumnDefine(tableName, it, nameType, columnName + spreadColumn.value, comment)
                    .apply {
                        list.addAll(this.first)
                        checks.addAll(this.second)
                    }
//                var columnNameValue = columnName + spreadColumn.value + it.name;
//
//                var sqlTypeString = it.getAnnotation(SqlColumnType::class.java)?.value
//                    .AsString {
//                        DbType.of(it.type)
//                            .toMySqlTypeString(getVarcharLen(it), getEnumItems(propertyType))
//                    }
//
//
//                var comment = it.getAnnotation(Cn::class.java)?.value.AsString()
//
//
//                var item =
//                    """`${columnNameValue}` ${sqlTypeString} not null ${if (it.type.IsNumberType) "default '0'" else if (propertyType.IsStringType) "default ''" else ""} comment '${comment}'"""
//                list.add(item);
            }

            return list to checks;
        } else if (dbType == DbType.JSON || dbType == DbType.OTHER) {
            //生成关系表
            if (propertyType.IsCollectionType) {
                var aryComment = comment;
                if (aryComment.contains("[]") == false) {
                    aryComment += " []"
                }
                var item =
                    """`${columnName}` Json not null  comment '${aryComment}'"""
                list.add(item);

                checks.add("CONSTRAINT `c_${columnName}` CHECK ( json_valid(`${columnName}`) )")
            } else {
                var objComment = comment;
                if (objComment.contains("{}") == false) {
                    objComment += " {}"
                }

                var item =
                    """`${columnName}` Json not null  comment '${objComment}'"""
                list.add(item);

                checks.add("CONSTRAINT `jc_${tableName}_${columnName}` CHECK ( json_valid(`${columnName}`) )")
            }
        } else {
            var item =
                """`${columnName}` ${type} not null ${if (propertyType.IsNumberType) "default '0'" else if (propertyType.IsStringType) "default ''" else ""} comment '${comment}'"""
            list.add(item);
        }

        return list to checks;
    }

    /**
     * 生成实体的 sql 代码
     */
    @JvmStatic
    fun entity2Sql(
        entity: Class<*>,
        nameType: NameMappingTypeEnum = NameMappingTypeEnum.ORIGIN
    ): String {
        var list = mutableListOf<String>();

        var fields = entity.AllFields
            .sortedBy {
                // id,code,name 这三个字段提前。
                if (it.name basicSame "id") return@sortedBy -9;
                if (it.name basicSame "code") return@sortedBy -8;
                if (it.name basicSame "name") return@sortedBy -7;


                // 其它系统字段最后
                if (it.name basicSame "remark") return@sortedBy 1000 + it.name.length;
                if (it.name.contains("delete", true)) return@sortedBy 1000 + it.name.length;
                if (it.name.contains("create", true)) return@sortedBy 1000 + it.name.length;
                if (it.name.contains("update", true)) return@sortedBy 1000 + it.name.length;
                return@sortedBy it.name.length;
            }

        var tableName = nameType.getResult(entity.simpleName)
        var checks = mutableListOf<String>();

        fields.forEach {
            getColumnDefine(tableName, it, nameType)
                .apply {
                    list.addAll(this.first);
                    checks.addAll(this.second);
                }
        }


        return """
DROP TABLE IF EXISTS `${tableName}`;
CREATE TABLE IF NOT EXISTS `${tableName}` (
${list.joinToString(const.line_break + ",")}
, PRIMARY KEY ( ${
            getPk(entity, nameType).map { "`${it}`" }.joinToString(", ").AsString("!没有主键!")
        } )
${checks.map { ", " + it }.joinToString("\n")}
) ENGINE=InnoDB  COMMENT='${entity.getAnnotation(Cn::class.java)?.value.AsString()}';
"""
    }

    /**
     * 从众多唯一索引中确定唯一索引。
     */
    fun getPk(
        entity: Class<*>,
        nameType: NameMappingTypeEnum = NameMappingTypeEnum.ORIGIN
    ): Set<String> {
        var autoIncKey = entity.getAnnotation(SqlAutoIncrementKey::class.java);


        if (autoIncKey != null) {
            return setOf(nameType.getResult(autoIncKey.value))
        }

        val indexes = entity.getAnnotation(DbEntityIndexes::class.java)
        if (indexes != null) {
            var ids = indexes.value.filter { it.unique };
            if (ids.any()) {
                return getPk(ids, nameType);
            }
        }

        val index = entity.getAnnotationsByType(DbEntityIndex::class.java)
        if (index != null && index.any()) {
            var ids = index.filter { it.unique }
            return getPk(ids, nameType)
        }

        return setOf()
    }

    private fun getPk(
        ids: List<DbEntityIndex>,
        nameType: NameMappingTypeEnum = NameMappingTypeEnum.ORIGIN
    ): Set<String> {
        return ids
            .sortedBy { it.value.size * 1000 + it.value.map { it.length }.count() }
            .first()
            .value
            .map { nameType.getResult(it) }
            .toSet()
    }

//    class field_name : TemplateMethodModelEx {
//        override fun exec(p0: MutableList<Any?>?): String {
//            var p1 = p0?.elementAt(0).AsString();
//            if (p1.isEmpty()) return "";
//
//            if (MyUtil.allCharIsSameCase(p1)) return p1.lowercase();
//
//            return StringUtil.getSmallCamelCase(p1);
//        }
//    }
}
